package cn.pugle.ormtest.ebean.domain.finder;

import cn.pugle.ormtest.ebean.domain.OrderModel2;
import cn.pugle.ormtest.ebean.domain.query.QOrderModel2;
import io.ebean.Finder;
import io.ebean.SqlRow;
import io.ebean.SqlUpdate;
import io.ebean.Update;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;

/**
 * @author tzp
 * @date 2017/12/27
 */
public class OrderFinder extends Finder<Long, OrderModel2> {


    public OrderFinder() {
        super(OrderModel2.class);
    }

    public OrderFinder(String serverName) {
        super(OrderModel2.class, serverName);
    }

    /**
     * 返回query bean
     */
    public QOrderModel2 where() {
        return new QOrderModel2(db());
    }

    //-- 在ebean里, query 和 sqlQuery是两个根接口, 互斥的.
    //-- query的一个实现是 DefaultOrmQuery, 其内部有俩奇怪成员变量: rawSql nativeSql.

    /**
     * 7天内的订单
     * raw query. 使用表名. 不能select *
     * eql query. 不是sql, 是querybean流式方法的字符串形式而已. 使用java bean.
     * native query.
     */
    public List<OrderModel2> recentOrders() {
        LocalDateTime recent_ = LocalDateTime.now().minusDays(7);
        Date recent = Date.from(recent_.atZone(ZoneId.systemDefault()).toInstant());

//        String sql = "select id,order_date,price,address from st_order2 where order_date >= :recent";
//        RawSql rawSql = RawSqlBuilder.parse(sql)
//                .create();
//        List<OrderModel2> orders = this.where().setRawSql(rawSql)
//      //          .orderDate.greaterOrEqualTo(recent)//这个不用上面的where,
//                .query().setParameter("recent", recent) //这个配合上面的where :recent
//                .findList();

//        //this.query(eql);//和下面一样.给这个起名叫eql query
//        String eql = "select (id,address) where orderDate >= :recent";//这是lazy load的, 后面如果不访问其他属性,就不会再查一次
//        List<OrderModel2> orders = this.db().createQuery(OrderModel2.class, eql)
//                .setParameter("recent", recent)
//                .findList();

        String sql = "select id,order_date,price,address from st_order2 where order_date >= :recent";
        //this.nativeSql(sql);
        List<OrderModel2> orders = this.db().findNative(OrderModel2.class, sql)//给这个起名叫native query
                .setParameter("recent", recent)
                .findList();

        return orders;
    }

    /**
     * 7天内的订单
     * sqlQuery 返回的是sqlRows. 使用表名.
     */
    public List<SqlRow> recentOrders1() {
        LocalDateTime recent_ = LocalDateTime.now().minusDays(7);
        Date recent = Date.from(recent_.atZone(ZoneId.systemDefault()).toInstant());

        String sql = "select id,order_date,price,address from st_order2 where order_date >= :recent";

        List<SqlRow> sqlRows = this.db().createSqlQuery(sql)
                .setParameter("recent", recent)
                .findList();

        return sqlRows;
    }

    public List<OrderModel2> recentOrdersLazy() {
        LocalDateTime recent_ = LocalDateTime.now().minusDays(7);
        Date recent = Date.from(recent_.atZone(ZoneId.systemDefault()).toInstant());

        String eql = "select (id,address) where orderDate >= :recent";
        return this.query(eql)
                .setParameter("recent", recent)
                .findList();
    }

    /**
     * 根据customerId来删除order
     * sql update: 使用表名和列名字
     */
    public int deleteByCustomerId(Long customerId) {
        String sql = "delete from st_order2 where customer_id = :id";
        SqlUpdate sqlUpdate = this.db().createSqlUpdate(sql)
                .setParameter("id", customerId);
        return sqlUpdate.execute();
    }

    /**
     * orm update: 使用java bean的名和属性名
     */
    public int updatePriceByCustomerId(Long customerId, Long newPrice) {
        String dml = "update OrderModel2 set price = :newPrice";
        Update<OrderModel2> update = this.db().createUpdate(OrderModel2.class, dml)
                .setParameter("id", customerId)
                .setParameter("newPrice", newPrice);

        return update.execute();
    }
}
